package top.anydata.lifeofcode.lifesystem.quartz;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 功能描述: 定时任务流程. 定时读取mysql数据.
 *
 * @author <a href="mailto:caingao@anydata.top"/>
 * @version V1.0.0
 * @date 2024/3/26 21:53
 */
@Slf4j
@Component
public class SqlDumpSchedulerTask {

    private JdbcTemplate adminJdbcTemplate;

    private JdbcTemplate appJdbcTemplate;

    public SqlDumpSchedulerTask(JdbcTemplate adminJdbcTemplate, JdbcTemplate appJdbcTemplate) {
        this.adminJdbcTemplate = adminJdbcTemplate;
        this.appJdbcTemplate = appJdbcTemplate;
    }

//    @Scheduled(fixedRate = 1000 * 60)
    public void dumpSql(){
        log.info("开始dump表结构");
        try {
            Connection connection = Objects.requireNonNull(adminJdbcTemplate.getDataSource()).getConnection();
            log.info("admin库连接:{}",connection);
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables("life_system_admin", null, "%", new String[]{"TABLE"});
            while (tables.next()){
                String tableName = tables.getString("TABLE_NAME");
                log.info("admin库表结构:{}",tableName);
                String createTableSql = adminJdbcTemplate.queryForObject(
                        "SHOW CREATE TABLE " + tableName,
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                                return rs.getString(2); //获取第二列的值,也就是建表语句.
                            }
                        }
                );
                log.info("admin库表结构:{}",createTableSql);
                List<Map<String, Object>> result = adminJdbcTemplate.queryForList("SELECT * FROM " + tableName);
                for (Map<String, Object> row : result) {
                    StringBuilder columns = new StringBuilder();
                    StringBuilder values = new StringBuilder();
                    for (Map.Entry<String, Object> entry : row.entrySet()) {
                        if (!columns.isEmpty()) {
                            columns.append(", ");
                            values.append(", ");
                        }
                        columns.append(entry.getKey());
                        if (entry.getValue() instanceof String) {
                            values.append("'").append(entry.getValue()).append("'");
                        } else {
                            values.append(entry.getValue());
                        }
                    }
                    String insertSql = "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + values + ")";
                    log.info("Insert SQL: {}", insertSql);
                }
            }
        } catch (SQLException e) {
            log.error("",e);
        }
        log.info("结束dump表结构");
    }

}
